package org.paradise.dms.dao.impl;

import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.type.Type;
import org.paradise.dms.pojo.AccomodationHistory;
import org.paradise.dms.pojo.AllocationInfo;
import org.paradise.dms.pojo.Dormitory;
import org.paradise.dms.pojo.Student;
import org.paradise.dms.pojo.StudentBedLink;
import org.paradise.dms.pojo.StudentReportDormCheckout;
import org.springframework.stereotype.Service;

import com.dheaven.framework.dao.BaseHibernateDao;
import com.dheaven.framework.dao.DaoException;

@Service
public class StudentReportDAOImpl  extends BaseHibernateDao<Student> {

	private static Logger log = Logger.getLogger(StudentReportDAOImpl.class);
	
	//不允许申请退宿标志
	public static int NOT_ALLOW_CHECK_OUT_TAG = -1000;
	
	public Student getStudent(String studentno,String password){
		List<Student> list = null;
		try {
			list = (List<Student>)this.find("from Student where studentno = ? and studentpassword=? and studentind = 1",
					new Object[] { studentno,password });
		} catch (DaoException e) {
			log.error("DMS_error:学生信息查询出错！");
			log.error("错误原因:" + e);
		}
		if(null == list || list.size()<1) {
			return null;
		}else {
			return list.get(0);
		}
		
	}
	public Student getStudentByNO(String studentno){
		List<Student> list = null;
		try {
			list = (List<Student>)this.find("from Student where studentno = ? and studentind = 1",
					new Object[] { studentno});
		} catch (DaoException e) {
			log.error("错误原因:" + e);
		}
		if(null == list || list.size()<1) {
			return null;
		}else {
			return list.get(0);
		}
		
	}
	
	public Student getStudentByIdentity(String identity){
		List<Student> list = null;
		try {
			list = (List<Student>)this.find("from Student where studentidentitycardno = ?  and studentind = 1",
					new Object[] { identity });
		} catch (DaoException e) {
			log.error("DMS_error:学生信息查询出错！");
			log.error("错误原因:" + e);
		}
		if(null == list || list.size()<1) {
			return null;
		}else {
			return list.get(0);
		}
		
	}
	//todo，单个申请宿舍时,获得第一个可分配的宿舍id
	public int getAvailDormitoryidByStudentNo(String stuno){
		Student s = this.getStudentByNO(stuno);
		
		StringBuffer sb = new StringBuffer();
		sb.append("select d.dormitoryid as dormitoryid,count(sbl.dormitorybedid) as inamount");
		sb.append(",d.dormitorylodgetype as dormitorylodgetype");
		sb.append(" from dormitory d left join studentbedlink sbl on d.dormitoryid=sbl.dormitoryid");
		sb.append(" where d.allocationid=");
		sb.append(s.getStudentpreasignedid());
		sb.append(" and d.dormitoryind=1 group by d.dormitoryid");
		sb.append(" having inamount<d.dormitorylodgetype");
		sb.append(" order by d.dormitorysort limit 1");
		
		System.out.print("----"+sb.toString());
		SQLQuery q = this.getSession().createSQLQuery(sb.toString());
		List l = q.list();
		if(l.size() <= 0 || null == l){
			return -1;
		}else {
			Object [] obj = (Object [])l.get(0);
			return  Integer.parseInt(obj[0].toString()) ;
		}
	}
	
	//根据id获得预分配表的信息
	public AllocationInfo getAllocationinfoById(String id){
		List<AllocationInfo> list = null;
		try {
			list = (List<AllocationInfo>)this.find("from AllocationInfo where allocationid = ?",
					new Object[] { id});
		} catch (DaoException e) {
			log.error("错误原因:" + e);
		}
		if(null == list || list.size()<1) {
			return null;
		}else {
			return list.get(0);
		}
		
	}
	
	public List findDormByStudentno(String stuno){
		Student s = this.getStudentByNO(stuno);
		StringBuffer sb = new StringBuffer();
		sb.append("select d.dormitoryid as dormitoryid ,d.dormitorydisplayname as dormitorydisplayname");
		sb.append(" from student s join studentbedlink sbl on s.studentid=sbl.studentid");
		sb.append(" join dormitory d on d.dormitoryid=sbl.dormitoryid");
		sb.append(" where s.studentno = '");
		sb.append(stuno);
		sb.append("'");
		System.out.print("----"+sb.toString());
		SQLQuery q = this.getSession().createSQLQuery(sb.toString());
		return q.list();
	}
	//4人及4人以下时只要一个房间,查找这样的一个可用房间
	public int getAvailOneDormitoryidByStudentnosForMultiApply(String[] stunos){
		Student s = this.getStudentByNO(stunos[0]);
		StringBuffer sb = new StringBuffer();
		sb.append("select d.dormitoryid as dormitoryid,count(sbl.dormitorybedid) as inamount");
		sb.append(",d.dormitorylodgetype as dormitorylodgetype");
		sb.append(" from dormitory d left join studentbedlink sbl on d.dormitoryid=sbl.dormitoryid");
		sb.append(" where d.allocationid=");
		sb.append(s.getStudentpreasignedid());
		sb.append(" and d.dormitoryind=1 group by d.dormitoryid");
		sb.append(" having (d.dormitorylodgetype-inamount >= ");
		sb.append(stunos.length);
		sb.append(") order by d.dormitorysort limit 1");
		
		System.out.print("----"+sb.toString());
		SQLQuery q = this.getSession().createSQLQuery(sb.toString());
		List l = q.list();
		if(l.size() <= 0 || null == l){
			return -1;
		}else {
			Object [] obj = (Object [])l.get(0);
			return  Integer.parseInt(obj[0].toString()) ;
		}
	}
	//5人及5人以上时要两个房间,即一个套间,查找这样的一个套间房间
	public int[] getAvailDormitoryidsByStudentnosForMultiApply(String[] stunos){
		Student s = this.getStudentByNO(stunos[0]);
		StringBuffer sb = new StringBuffer();
		//todo ,未测试过
		sb.append("select suitename,sum(inamount) as inamount,sum(dormitorylodgetype) as dormitorylodgetype");
		sb.append(" from dormwithstudent where allocationid=" );
		sb.append(s.getStudentpreasignedid());
		sb.append(" group by suitename  having (dormitorylodgetype - inamount >= ");
		sb.append(stunos.length);
		sb.append(") order by dormitorysort");
		
		System.out.println("-----------"+sb.toString());
		SQLQuery q = this.getSession().createSQLQuery(sb.toString());
		List l = q.list();
		if(l.size() <= 0 || null == l){
			return null;
		}else {
			Object [] obj0 = (Object [])l.get(0);
			List<Dormitory> list = (List<Dormitory>)this.find("from Dormitory where suitename='" +obj0[0].toString() +"'");
			int [] ia = new int[]{-1,-1};
			if(list.size()==1){   //如果是一间房只返回一个dormid,另一个是默认-1
				ia[0] = list.get(0).getDormitoryid() ;
				return  ia ;
			}else{  //如果是一个套房（两间）返回两个dormid
				ia[0] = list.get(0).getDormitoryid() ;
				ia[1] = list.get(1).getDormitoryid();
				return  ia ;
			}
			
		}
	}
	public List<Student> getStudentsByNOs(String [] stunos){
		List<Student> list = null;
		String ids = "'-1'";   //此处的-1没有特别的意思,只是为了拼字串符的方便
		for(String s : stunos){
			System.out.println("==========="+ s);
			if(!"".equals(s)){
				ids += (",'" + s + "'");	
			}
		}
		System.out.println("from Student where studentno in (" +ids +")");
		list = (List<Student>)this.find("from Student where studentno in (" +ids +")");
		
		if(null == list || list.size()<1) {
			return null;
		}else {
			return list;
		}
	}
	
	public int cancelAllocate(String studentno){
		List<Student> sl = (List<Student>)this.find("from Student where studentno = '" + studentno+"'");
		List<StudentReportDormCheckout> srdcList  = (List<StudentReportDormCheckout>)this.find("from StudentReportDormCheckout where studentno = '"+studentno+"'");
		if(srdcList != null && srdcList.size()>1){
			return NOT_ALLOW_CHECK_OUT_TAG;
		}
		String sqlDelete ;
		//删除历史住宿信息，如果有的话
		System.out.println("删除历史住宿信息前-----------");
		List ah = this.getSession().createSQLQuery("select accomodationhistoryid from accomodationhistory where studentid ="+sl.get(0).getStudentid() +" order by accomodationhistoryid desc limit 1").list();
		if(ah != null && ah.size() > 0){
			Object [] obj = (Object [])(ah.get(0));
			sqlDelete = "delete accomodationhistory  where accomodationhistoryid = "+(String)obj[0];
			this.getSession().createSQLQuery(sqlDelete).executeUpdate();
		}
		System.out.println("删除历史住宿信息后-----------"+ah.size());
		
		
		//删除住宿床位分配信息
		String hqlDelete = "delete StudentBedLink s where s.studentid = "+sl.get(0).getStudentid();
		return this.getSession().createQuery(hqlDelete).executeUpdate();
		 
	}
	
	//插入新生报到退宿记录表(studentreportdormcheckout表)信息
	public int insertStudentreportdormcheckout(StudentReportDormCheckout srdc){
		SQLQuery query = (SQLQuery) this.getSession().createSQLQuery("insert into studentreportdormcheckout(studentid,studentno,dormitoryid) values(?,?,?)");
		query.setParameter(0, srdc.getStudentid());
		query.setParameter(1, srdc.getStudentno());
		query.setParameter(2, srdc.getDormitoryid());
		return query.executeUpdate();
	}
	
	
	public List queryBySQL(String sql){
		SQLQuery q = this.getSession().createSQLQuery(sql);
		return q.list();
	}
	public int queryCountBySQL(String sql){
		SQLQuery q = this.getSession().createSQLQuery(sql);
		int count = ((Number) q.uniqueResult()).intValue();
		return count;
	}
	
	//从新生报道退宿记录表查找退宿记录
	public List findDormCheckOut(String studentno){
		return this.find("from StudentReportDormCheckout where studentno = '"+studentno+"'");
	}
	
}
